from fastapi import APIRouter, Depends, HTTPException, Query, Body, Path
from typing import List, Optional, Dict, Any
from pydantic import BaseModel, Field
import time
import uuid
import sqlite3
from datetime import datetime
from sqlalchemy.orm import Session

from ..database.database import get_db
from ..models.shop import Product, Shop
from .auth import get_current_active_user
from ..utils.db import get_db_connection
from ..utils.logger import get_logger

logger = get_logger(__name__)
router = APIRouter(tags=["商品管理"])

# 定义请求和响应模型
class ItemBase(BaseModel):
    item_name: str
    item_brand: Optional[str] = None
    item_specs: Optional[str] = None
    item_price: float
    base_price: Optional[float] = None
    base_unit: Optional[str] = None
    item_classification: Optional[str] = None
    shop_id: int
    sku_id: str
    item_img: str
    run_id: str

class ItemCreate(ItemBase):
    pass

class ItemResponse(ItemBase):
    id: int
    created_at: Optional[datetime] = None
    updated_at: Optional[datetime] = None
    shop_name: Optional[str] = None

    class Config:
        from_attributes = True

class BulkDeleteRequest(BaseModel):
    ids: List[int]

class ResponseModel(BaseModel):
    code: int = 0
    message: str = "success"
    data: Optional[Any] = None

# 路由处理函数
@router.get("/", response_model=dict)
async def get_items(
    page: int = Query(1, description="页码"),
    page_size: int = Query(10, description="每页记录数"),
    keyword: Optional[str] = Query(None, description="商品名称关键词"),
    brand: Optional[str] = Query(None, description="品牌关键词"),
    shop_id: Optional[int] = Query(None, description="店铺ID"),
    start_date: Optional[datetime] = Query(None, description="开始时间"),
    end_date: Optional[datetime] = Query(None, description="结束时间"),
    db: Session = Depends(get_db)
):
    logger.info(f"时间：{start_date} - {end_date}")
    """获取商品列表"""
    try:
        query = db.query(Product)
        
        # 添加过滤条件
        if keyword:
            query = query.filter(Product.item_name.like(f"%{keyword}%"))
        if brand:
            query = query.filter(Product.item_brand.like(f"%{brand}%"))
        if shop_id:
            query = query.filter(Product.shop_id == shop_id)
        if start_date and end_date:
            query = query.filter(Product.created_at >= start_date).filter(Product.created_at <= end_date)
        
        # 获取总数
        total = query.count()
        
        # 获取分页数据
        items = query.order_by(Product.created_at.desc()) \
                    .offset((page - 1) * page_size) \
                    .limit(page_size) \
                    .all()
        
        return {
            "items": items,
            "total": total
        }
    except Exception as e:
        logger.error(f"获取商品列表失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"获取商品列表失败: {str(e)}")

@router.get("/{item_id}", response_model=ItemResponse)
async def get_item(
    item_id: int,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    """获取商品详情"""
    try:
        item = Product.get_by_id(db, item_id)
        if not item:
            raise HTTPException(status_code=404, detail="商品不存在")
        return item
    except Exception as e:
        logger.error(f"获取商品详情失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"获取商品详情失败: {str(e)}")

@router.post("/", response_model=ItemResponse, status_code=201)
async def create_item(
    item: ItemCreate,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    """创建商品"""
    try:
        # 创建商品实例
        new_item = Product(
            item_name=item.item_name,
            item_brand=item.item_brand,
            item_specs=item.item_specs,
            item_price=item.item_price,
            base_price=item.base_price,
            base_unit=item.base_unit,
            item_img=item.item_img,
            item_classification=item.item_classification,
            shop_id=item.shop_id,
            sku_id=item.sku_id,
            run_id=item.run_id
        )
        
        # 根据 sku_id 同步 item_sku 信息
        if new_item.sku_id:
            matching_sku = db.query(models.ItemSKU).filter(
                models.ItemSKU.sku == new_item.sku_id
            ).first()
            
            if matching_sku:
                new_item.cleaned_name = matching_sku.cleaned_name
                new_item.item_brand = matching_sku.item_brand
                new_item.item_specs = matching_sku.item_specs
                new_item.base_unit = matching_sku.base_unit
                new_item.package_type = matching_sku.package_type
            else:
                raise HTTPException(
                    status_code=400,
                    detail=f"找不到对应的 SKU 信息: {new_item.sku_id}"
                )
                
        # 保存商品
        new_item.save(db)
        
        # 获取完整信息（包括shop_name）
        saved_item = Product.get_by_id(db, new_item.id)
        return saved_item
    except Exception as e:
        logger.error(f"创建商品失败: {str(e)}")
        raise HTTPException(status_code=400, detail=f"商品创建失败: {str(e)}")

@router.put("/{item_id}", response_model=ItemResponse)
async def update_item(
    item_id: int,
    item: ItemBase,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    """更新商品"""
    try:
        # 检查商品是否存在
        existing_item = Product.get_by_id(db, item_id)
        if not existing_item:
            raise HTTPException(status_code=404, detail="商品不存在")
        
        # 更新商品属性
        for key, value in item.dict().items():
            setattr(existing_item, key, value)
            
        # 根据 sku_id 同步 item_sku 信息
        if existing_item.sku_id:
            matching_sku = db.query(models.ItemSKU).filter(
                models.ItemSKU.sku == existing_item.sku_id
            ).first()
            
            if matching_sku:
                existing_item.cleaned_name = matching_sku.cleaned_name
                existing_item.item_brand = matching_sku.item_brand
                existing_item.item_specs = matching_sku.item_specs
                existing_item.base_unit = matching_sku.base_unit
                existing_item.package_type = matching_sku.package_type
        
        # 保存更新
        existing_item.save(db)
        
        # 获取完整信息（包括shop_name）
        updated_item = Product.get_by_id(db, item_id)
        return updated_item
    except Exception as e:
        logger.error(f"更新商品失败: {str(e)}")
        raise HTTPException(status_code=400, detail=f"商品更新失败: {str(e)}")

@router.delete("/{item_id}", status_code=204)
async def delete_item(
    item_id: int,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    """删除商品"""
    try:
        # 检查商品是否存在
        existing_item = Product.get_by_id(db, item_id)
        if not existing_item:
            raise HTTPException(status_code=404, detail="商品不存在")
        
        # 删除商品
        existing_item.delete(db)
        return None
    except Exception as e:
        logger.error(f"删除商品失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"删除商品失败: {str(e)}")

@router.delete("/batch", status_code=204)
async def batch_delete_items(
    item_ids: List[int],
    db: Session = Depends(get_db),
    current_user = Depends(get_current_active_user)
):
    """批量删除商品"""
    try:
        if len(item_ids) == 0:
            raise HTTPException(status_code=400, detail="商品ID列表为空")
        
        Product.bulk_delete(db, item_ids)
        return None
    except Exception as e:
        logger.error(f"批量删除商品失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"批量删除商品失败: {str(e)}")

@router.get("/stats", response_model=ResponseModel)
async def get_item_stats(
    db: Session = Depends(get_db),
    current_user: dict = Depends(get_current_active_user)
):
    """获取商品统计信息"""
    try:
        # 获取商品总数
        total_items = db.query(Product).count()
        
        # 获取品牌分布
        brands = db.query(
            Product.item_brand,
            db.func.count(Product.id).label('count')
        ).filter(Product.item_brand != '') \
         .group_by(Product.item_brand) \
         .order_by(db.desc('count')) \
         .limit(10) \
         .all()
        
        # 获取店铺商品分布
        shops = db.query(
            Shop.id,
            Shop.shop_name,
            db.func.count(Product.id).label('count')
        ).outerjoin(Product, Shop.id == Product.shop_id) \
         .group_by(Shop.id) \
         .order_by(db.desc('count')) \
         .limit(10) \
         .all()
        
        # 获取价格区间分布
        price_ranges = []
        ranges = [
            (0, 10, '0-10元'),
            (10, 50, '10-50元'),
            (50, 100, '50-100元'),
            (100, 500, '100-500元'),
            (500, 1000, '500-1000元'),
            (1000, float('inf'), '1000元以上')
        ]
        
        for start, end, label in ranges:
            if end == float('inf'):
                count = db.query(Product).filter(Product.item_price >= start).count()
            else:
                count = db.query(Product).filter(
                    Product.item_price >= start,
                    Product.item_price < end
                ).count()
            
            price_ranges.append({
                'range': label,
                'count': count
            })
        
        return {
            'code': 0,
            'message': 'success',
            'data': {
                'total_items': total_items,
                'brands': {
                    'total': len(brands),
                    'items': [{'name': b.item_brand, 'count': b.count} for b in brands]
                },
                'shops': {
                    'total': len(shops),
                    'items': [{'id': s.id, 'name': s.shop_name, 'count': s.count} for s in shops]
                },
                'price_ranges': price_ranges
            }
        }
        
    except Exception as e:
        logger.error(f"获取商品统计信息失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"获取商品统计信息失败: {str(e)}")

@router.get("/sku/{sku_id}", response_model=ResponseModel)
async def get_item_by_sku(
    sku_id: str,
    db: Session = Depends(get_db),
    current_user: dict = Depends(get_current_active_user)
):
    """根据SKU ID获取商品信息"""
    try:
        item = db.query(Product).filter(Product.sku_id == sku_id).first()
        
        if not item:
            raise HTTPException(status_code=404, detail=f"商品不存在: SKU ID {sku_id}")
        
        return {
            'code': 0,
            'message': 'success',
            'data': item
        }
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"根据SKU ID获取商品信息失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"根据SKU ID获取商品信息失败: {str(e)}") 